package com.project.utils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.CellType;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * @author gaowei
 * 
 */
public class JExcelUtils {
	private WritableWorkbook writeBook;
	private WritableSheet writeSheet;
    private OutputStream outPut;
    private HttpServletResponse servletResponse;
	/**
	 * 
	 * @param path
	 * @param sheetName
	 * @param titles
	 */
	public void createExcelFile(String path, String sheetName,
			List<String> titles)  {
		try {
			outPut = new FileOutputStream(path);
			writeBook = Workbook.createWorkbook(outPut);
			writeSheet = writeBook.createSheet(sheetName, 0);
			Label label;
			WritableCellFormat format = getTitleCellFormat();
            for(int i =0;i<titles.size();i++)
            {
            	label = new Label(i, 0, titles.get(i), format);
            	writeSheet.addCell(label);
            }
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	public void  setContentHead(HttpServletResponse servletStream,String contentType,String headerName,String headerValue)
	{
		
		try {
			servletResponse = servletStream;
			servletResponse.setContentType(contentType);
			servletResponse.setHeader(headerName, headerValue);
			outPut = servletResponse.getOutputStream();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public void createExcelFile( String sheetName,
			List<String> titles)  {
		try {
			//outPut = new FileOutputStream(path);
			
			
			writeBook = Workbook.createWorkbook(outPut);
			writeSheet = writeBook.createSheet(sheetName, 0);
			Label label;
			WritableCellFormat format = getTitleCellFormat();
            for(int i =0;i<titles.size();i++)
            {
            	label = new Label(i, 0, titles.get(i), format);
            	writeSheet.addCell(label);
            }
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	    /** 
	    * 第一行表头的格式
	    * @return 
	    */  
	   public WritableCellFormat getTitleCellFormat(){  
	       WritableCellFormat wcf = null;  
	       try {  
	           //字体样式  
	           WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic  
	           wf.setColour(Colour.RED);  
	           wcf = new WritableCellFormat(wf);  
	           //对齐方式  
	           wcf.setAlignment(Alignment.CENTRE);  
	           wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
	           //边框  
	           wcf.setBorder(Border.ALL,BorderLineStyle.THIN);  
	             
	           //背景色  
	           wcf.setBackground(Colour.GREY_25_PERCENT);  
	       } catch (WriteException e) {  
	        e.printStackTrace();  
	       }  
	       return wcf;  
	   }  
	           /** 
	       * 得到数据格式 
	       * @return 
	       */  
	      public WritableCellFormat getDataCellFormat(CellType type){  
	          WritableCellFormat wcf = null;  
	          try {  
	              //字体样式  
	              if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字  
	                 NumberFormat nf = new NumberFormat("#.00");  
	                 wcf = new WritableCellFormat(nf);   
	              }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期  
	                  jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");   
	                  wcf = new jxl.write.WritableCellFormat(df);   
	              }else{  
	                  WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic  
	                  wcf = new WritableCellFormat(wf);  
	              }  
	              //对齐方式  
	              wcf.setAlignment(Alignment.CENTRE);  
	              wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
	              //边框  
	              wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);  
	              wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);  
	              wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);  
	              //背景色  
	              wcf.setBackground(Colour.WHITE);  
	                
	              wcf.setWrap(true);//自动换行  
	                
	          } catch (WriteException e) {  
	           e.printStackTrace();  
	          }  
	          return wcf;  
	      }  
      
      /**
     * @param row   开始的行号
     * @param data  写入的数据
     */
    public void insertData(int row,List<List<String>> data)
 {
		try {
			Label label;
			WritableCellFormat foramt = getDataCellFormat(CellType.STRING_FORMULA);
			for (int i = 0; i < data.size(); i++) {
				List<String> innerData = data.get(i);
				int len = innerData.size();
				for (int j = 0; j < len; j++) {
					label = new Label(j, row, innerData.get(j), foramt);
					writeSheet.addCell(label);
				}
				row++;
			}
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    public void write()
    {
    	try {
			writeBook.write();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
    public void flushAndClose()
    {
    	try {
    		//writeBook.write();
			writeBook.close();
			outPut.flush();
			outPut.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
}